package fashionshop.dal;

import java.math.BigDecimal;
import java.sql.ResultSet;

import database.MySQLConnect;
import fashionshop.bll.CategoryBLL;
import fashionshop.bll.ProductBLL;
import fashionshop.bll.StaffBLL;

public class ProductDAL {

	public ProductDAL(){
	}
	
	public static ResultSet LoadAll() throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery = "SELECT product.*, category.Name as CategoryName FROM product, category WHERE product.CategoryID = category.ID";
		return conn.executeQuery(sqlQuery);
	}

	public static ResultSet LoadByID(int id) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT product.*, category.Name as CategoryName FROM product, category WHERE product.CategoryID = category.ID and product.ID = '%d'";
		String sqlQuery = String.format(sql, id);
		return conn.executeQuery(sqlQuery);
	}
	
	// Find all fields by keywork
	public static ResultSet LoadByKeywork(String keywork) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT DISTINCT product.*, category.Name as CategoryName FROM product, category WHERE product.CategoryID = category.ID and (category.Name LIKE '%" + keywork + "%' or product.Name LIKE '%" + keywork + "%' or Code LIKE '%" + keywork + "%' or Image LIKE '%" + keywork + "%' or Color LIKE '%" + keywork + "%' or Description LIKE '%" + keywork + "%')";
		return conn.executeQuery(sql);
	}
	
	// Advance Search
	public static ResultSet LoadByCriteria(ProductBLL product, BigDecimal from, BigDecimal to) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT product.*, category.Name as CategoryName FROM product, category WHERE product.CategoryID = category.ID and (product.Name LIKE '%" + product.getName() + "%' and Code LIKE '%" + product.getCode() + "%' and Color LIKE '%" + product.getColor() + "%' and Description LIKE '%" + product.getDescription() + "%'";
		if(product.getCategoryId()!= 0)
			sql = sql + " and CategoryID = " + product.getCategoryId();
		if(from != null)
			sql = sql +" and Price >= " + from;
		if(to != null)
			sql = sql + " and Price <= " + to;
		sql = sql + ")";
		return conn.executeQuery(sql);
	}
	
	public static int Insert(ProductBLL product) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "INSERT INTO product (`CategoryID`, `Name`, `Code`, `Quantity`, `Image`, `Color`, `Price`, `Description`, `CreateDate`, `Active`, `SalePrice`) VALUES ('%d', '%s', '%s', '%d', '%s', '%s', '%.0f', '%s', '%s', '%d', '%.0f')";
		String sqlQuery = String.format(sql, product.getCategoryId(), product.getName(), product.getCode(), product.getQuantity(), product.getImage(), product.getColor(), product.getPrice(), product.getDescription(), product.getCreateDate(), product.getActive(), product.getSalePrice());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(ProductBLL product) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE product SET `CategoryID`='%d', `Name`='%s', `Code`='%s', `Quantity`='%d', `Image`='%s', `Color`='%s', `Price`='%.0f', `Description`='%s', `Active`='%d', `SalePrice`='%.0f' WHERE `ID`='%d'";
		String sqlQuery = String.format(sql, product.getCategoryId(), product.getName(), product.getCode(), product.getQuantity(), product.getImage(), product.getColor(), product.getPrice(), product.getDescription(), product.getActive(), product.getSalePrice(), product.getId());
		return conn.executeUpdate(sqlQuery);
	}
	
//	public static int Delete(ProductBLL product) throws Exception{
//		MySQLConnect conn = new MySQLConnect();
//		String sql = "UPDATE product SET `Active`='0' WHERE `ID`='%d'";
//		String sqlQuery = String.format(sql, product.getId());
//		return conn.executeUpdate(sqlQuery);
//	}
//	
//	public static int Delete(int id) throws Exception{
//		MySQLConnect conn = new MySQLConnect();
//		String sql = "UPDATE product SET `Active`='0' WHERE `ID`='%d'";
//		String sqlQuery = String.format(sql, id);
//		return conn.executeUpdate(sqlQuery);
//	}
	
	// Delete all product belong to this Category
	public static int Delete(CategoryBLL category) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE product SET `Active`='0' WHERE `CategoryID`='%d'";
		String sqlQuery = String.format(sql, category.getId());
		return conn.executeUpdate(sqlQuery);
	}
	
}
